package ca.ualberta.cs.CLRDB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;

import ca.ualberta.cs.DB2ExceptionHandling.DB2SqlException;

import uk.ac.liv.c3connector.AnnotationModel;

public class TagsManagement extends EntityRelManagement {

	public TagsManagement(){
		mainTableName = "Tagging";
	}
	
	public void initialize(Statement statement) throws SQLException{
		
		try{
			statement.executeUpdate("DROP TABLE Tag");
			statement.executeUpdate("DROP TABLE Tagging");
			statement.executeUpdate("DROP TABLE Categories");
			statement.executeUpdate("DROP TABLE Keywords");
			statement.executeUpdate("DROP TABLE TagCount");
		}catch(Exception e){}
		
		statement.executeUpdate("CREATE TABLE Tag ( " +
				"ID INTEGER GENERATED ALWAYS AS " +
					"IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL, " +
				//"labels CLOB(50K), " + //should change to varchar in order to use 'like'
				"labels VARCHAR(32600), " +
				"CONSTRAINT id_uniq PRIMARY KEY(ID) " +
		") IN CLRSPACE");
		
		
		statement.executeUpdate("CREATE TABLE Tagging ( " +
				"tagId INTEGER NOT NULL, " +
				"label VARCHAR(100) NOT NULL, " +
				"personId INTEGER NOT NULL, " +
				"resourceId INTEGER NOT NULL, " +
				"pageNumber INTEGER NOT NULL, " +
				"date Date," +
				"CONSTRAINT FK_resourceid FOREIGN KEY (resourceId) " +
                	"REFERENCES Resource (ID) ON DELETE CASCADE, " +
                "CONSTRAINT FK_personid FOREIGN KEY (personId) " +
                	"REFERENCES Person (ID) ON DELETE CASCADE, " +
                "CONSTRAINT FK_tagid FOREIGN KEY (tagId) " +
                	"REFERENCES Tag (ID) ON DELETE CASCADE, " +
                "CONSTRAINT PK_tagging PRIMARY KEY (resourceId, personId, pageNumber, label) " +     
		") IN CLRSPACE");
		numOfCols = 6;
		
		statement.executeUpdate("CREATE TABLE Categories ( " +
				"superset INTEGER NOT NULL, " +
				"subset INTEGER NOT NULL, " +
				"CONSTRAINT FK_super FOREIGN KEY (superset) " +
            		"REFERENCES Tag (ID) ON DELETE CASCADE, " +
            	"CONSTRAINT FK_sub FOREIGN KEY (subset) " +
            		"REFERENCES Tag (ID) ON DELETE CASCADE, " +
				"CONSTRAINT PK_cat PRIMARY KEY (superset, subset) " +
		") IN CLRSPACE");
		
		statement.executeUpdate("CREATE TABLE Keywords ( " +				
				"label VARCHAR(100) NOT NULL, " +				
				"bibtexId INTEGER NOT NULL, " +				
				"CONSTRAINT FK_bibtexid FOREIGN KEY (bibtexId) " +
                	"REFERENCES Bibtex (ID) ON DELETE CASCADE, " +                
                "CONSTRAINT PK_keywords PRIMARY KEY (bibtexId, label) " +     
		") IN CLRSPACE");
		
		statement.executeUpdate("CREATE TABLE TagCount ( " +
				"tagId INTEGER NOT NULL, " +
				"label VARCHAR(100) NOT NULL, " +				
				"resourceId INTEGER NOT NULL, " +
				"num INTEGER NOT NULL DEFAULT 1, " +				
				"CONSTRAINT FK_resourceid FOREIGN KEY (resourceId) " +
                	"REFERENCES Resource (ID) ON DELETE CASCADE, " +                
                "CONSTRAINT FK_tagid FOREIGN KEY (tagId) " +
                	"REFERENCES Tag (ID) ON DELETE CASCADE, " +
                "CONSTRAINT PK_tagging PRIMARY KEY (resourceId, label) " +     
		") IN CLRSPACE");
		
		statement.executeUpdate("CREATE TRIGGER tagCountUpdate AFTER INSERT ON Tagging " +
   				"REFERENCING NEW AS n " +
				"FOR EACH ROW  MODE DB2SQL " + 
				"BEGIN ATOMIC " + 
				"DECLARE x INT; " +
				"SET x = (SELECT COUNT(*) FROM TagCount WHERE label = n.label AND resourceid = n.resourceid ); " +
				"IF x = 0 THEN " +
				"INSERT INTO TagCount (tagId,label,resourceId,num) " +
					"VALUES (n.tagId,n.label,n.resourceId,1) ;" +
				"ELSE " +
				"UPDATE TagCount SET num = num+1 WHERE  label = n.label AND resourceid = n.resourceid ;" +
				"END IF; " +
				"END");
	}
	
	public void changeTaggingTable(){
		Connection con = DatabaseController.connectToDB();
		   Statement statement = null;
		   	
		   	try {			
		   		statement = con.createStatement();
		   		
		   		try{
		   			statement.executeUpdate("DROP TABLE Tagging");
		   		}catch(Exception e){}
		   		
		   		statement.executeUpdate("CREATE TABLE Tagging ( " +
						"tagId INTEGER NOT NULL, " +
						"label VARCHAR(100) NOT NULL, " +
						//"personId INTEGER NOT NULL, " +
						"resourceId INTEGER NOT NULL, " +
						"pageNumber INTEGER NOT NULL, " +
						//"date Date," +
						"frequency INTEGER NOT NULL WITH DEFAULT 1, " +
						"CONSTRAINT FK_resourceid FOREIGN KEY (resourceId) " +
		                	"REFERENCES Resource (ID) ON DELETE CASCADE, " +
		                /*"CONSTRAINT FK_personid FOREIGN KEY (personId) " +
		                	"REFERENCES Person (ID) ON DELETE CASCADE, " +*/
		                "CONSTRAINT FK_tagid FOREIGN KEY (tagId) " +
		                	"REFERENCES Tag (ID) ON DELETE CASCADE, " +
		                //"CONSTRAINT PK_tagging PRIMARY KEY (resourceId, personId, pageNumber, label) " +
	                	"CONSTRAINT PK_tagging PRIMARY KEY (resourceId, pageNumber, label) " +
				") IN CLRSPACE");
				numOfCols = 5;
		   		
		   		statement.close();
				   
			   con.commit();
			   con.close();				   
			   				
			
			} catch (SQLException ex) {
				DB2SqlException.printSqlErrorMsg(ex);		
			}
	}
	
	public void addTaggings(Collection<String> tags, Integer resourceId, Integer personId, int page, Date date, Statement statement) {
		for(String tag: tags)
			addTagging(tag, resourceId, personId, page, date, statement);
	}
	
	public int addTagging(String label, Integer resourceId, Integer personId, int page, Date date, Statement statement) {
		
		Integer id = null;
		
	   try{
		   Integer tagId = labelExists(label, statement);
		 
		   if( tagId < 0 )
			   tagId = addLabel(label, statement);
		   
		   String keys = "(resourceId,personId,label,pageNumber,tagId"+ (date!=null?",date":"")+")";
		   String dateS = null;
		   if(date != null)
			   dateS = new java.sql.Date((date.getTime())).toString();
		   
		   String vals = "("+ resourceId + ","+ personId+ ",'"+label.trim()+"',"+page+ ","+tagId + (dateS!=null?",'"+dateS+"'":"")+ ")";		   		   
		   
		   statement.executeUpdate("INSERT INTO Tagging "+keys+" VALUES " +vals , Statement.RETURN_GENERATED_KEYS);
		   ResultSet resId = statement.getGeneratedKeys();
		   resId.next();
		   id = resId.getInt(1);
	   }catch(SQLException e){ //unique constraint disturbed
		   try{
			   if(e.getErrorCode() != -803 )
				   throw e;
			   else{
				  ResultSet re = statement.executeQuery("SELECT tagId FROM Tagging WHERE (resourceId,personId,label,pageNumber) " +
				  		"= ("+ resourceId + ","+ personId+ ",'"+label+"',"+page+")");
				  if(re.next())
					  id = re.getInt("tagId");
			   }
		   }catch(SQLException e2){
			   DB2SqlException.printSqlErrorMsg(e2);
		   }
	   }
	   
	   return id;
		   
	}
	
	public int addTagging(AnnotationModel am, String username){
		int id = -1;
		
		Connection con = DatabaseController.connectToDB();
		
		try{
			Statement statement = con.createStatement();
			
			ResultSet resU = statement.executeQuery("SELECT ID FROM Person WHERE username = '" + username + "'");
			resU.next();
			Integer personId = resU.getInt("ID");
			
			Integer resourceId = -1;
			if(am.getResourceId() != null )
				resourceId = am.getResourceId();
			else{
				resourceId = ResourceManagement.resourceExists(am.resourceUri, con);
				if(resourceId == -1) //not exists
					resourceId = ResourceManagement.addResource(am.resourceUri,am.getDocumentDigest(),am.getDocumentTextDigest(),con);
			}
			
			String[] labels = am.getAnnotationBody().split("<content>")[1].split("</content>")[0].split(" &lt;br/&gt;");
			
			for(String label : labels)
				if(label.trim().length() > 0)
					addTagging(label, resourceId, personId, am.getPageNumber(), new Date(), statement);			
			
			statement.close();
			con.commit();
		
		} catch (SQLException e) {
			DB2SqlException.printSqlErrorMsg(e);		
		} catch (Exception e) {			
			e.printStackTrace();			
		}
		return id;
	}
	
	/**
	 * 
	 * @param label
	 * @param statement
	 * @return tag id, if not exists: -1
	 * @throws SQLException 
	 */
	public int labelExists(String label, Statement statement) throws SQLException{		
		ResultSet res = statement.executeQuery("SELECT ID FROM Tag WHERE labels LIKE '%"+label+"%'");
		if(res.next())
			return res.getInt("ID");
		else
			return -1;
	}
	
	public int addLabel(String label, Statement statement) throws SQLException{ 	//which considers synonyms
		int id = -1;
		
		int synonyms = findSynonyms(label, statement);
		if(synonyms == -1){ //insert
			statement.executeUpdate("INSERT INTO Tag (labels) VALUES ('" + label + "')",Statement.RETURN_GENERATED_KEYS);
			ResultSet res = statement.getGeneratedKeys();
			res.next();
			id = res.getInt(1);
		}
		else{
			//update
		}
		return id;
	}
	
	/**
	 * 
	 * @param label
	 * @param statement
	 * @return id of the synonyms in Tag table, if no synonyms: return -1
	 * @throws SQLException 
	 */
	public int findSynonyms(String label, Statement statement) throws SQLException{		
		return -1;
	}
	
	public ArrayList<String> findResourceTagsByURL(String url, Connection con){
		ArrayList<String> tags = new ArrayList<String>();
		
		if(con == null)
			con = DatabaseController.connectToDB();
		
		try{
			Statement statement = con.createStatement();
			
			ResultSet result = statement.executeQuery("SELECT x.label FROM Tagging x, Resource y WHERE "+
					"x.resourceId = y.ID AND y.url = '"+ url +"'" ); 
			
			while(result.next()){
				String label = result.getString("label");
				if(!tags.contains(label) && !tags.contains(label.trim()))
					tags.add(label.trim());
			}
			
			statement.close();
			con.commit();
		
		} catch (SQLException e) {
			DB2SqlException.printSqlErrorMsg(e);			
		} catch (Exception e) {			
			e.printStackTrace();			
		}
		
		return tags;
	}
	
	public ArrayList<String> findResourceTagsByDigestAndBib(String url, Connection con){
		ArrayList<String> tags = new ArrayList<String>();
		
		if(con == null)
			con = DatabaseController.connectToDB();
		
		try{
			Statement statement = con.createStatement();
			
			ResultSet result1 = statement.executeQuery("SELECT documentdigest, bibtexid FROM Resource WHERE "+
					"url = '"+ url +"'" );
			
			String docDigest = "-1";
			int bibtexid = -1;
			if(result1.next()){
				docDigest = result1.getString("documentdigest");
				bibtexid = result1.getInt("bibtexid");
			}
			
			ResultSet result = statement.executeQuery("SELECT DISTINCT x.label FROM Tagging x, Resource y WHERE "+
					"x.resourceId = y.ID AND (y.url = '"+ url +"' OR y.documentdigest = '"+docDigest+"'" +
							" or y.bibtexid = "+bibtexid+")" ); 
			
			while(result.next()){
				String label = result.getString("label");
				if(!tags.contains(label.trim()))
					tags.add(label.trim());
			}
			
			statement.close();
			con.commit();
		
		} catch (SQLException e) {
			DB2SqlException.printSqlErrorMsg(e);			
		} catch (Exception e) {			
			e.printStackTrace();			
		}
		
		return tags;
	}

	public void addKeywords(Collection<String> keys, Integer bibtexId, Statement statement){
		try{
			boolean innerStatement = false;
			Connection con = null;
			if(statement == null){
				con = DatabaseController.connectToDB();
				statement = con.createStatement();
				innerStatement = true;
			}
			for(String key: keys)
				addKeyword(key, bibtexId, statement);
			
			if(innerStatement){
				statement.close();
				con.commit();
				con.close();
			}
			
		}catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
	}
	
	public void addKeyword(String label, Integer bibtexId, Statement statement){
		try{			   
			   
			   String keys = "(bibtexId,label)";			   			   
			   String vals = "("+ bibtexId + ",'"+label+"')";		   		   
			   
			   statement.executeUpdate("INSERT INTO Keywords "+keys+" VALUES " +vals);
			   
	   }catch(SQLException e){
		   DB2SqlException.printSqlErrorMsg(e);
	   }
	}
	
	public HashMap<String,Integer> tagCloudByResourceId(int resourceid){
		HashMap<String,Integer> cloud = new HashMap<String, Integer>();
		
		try{			
			Connection con = DatabaseController.connectToDB();			 
			Statement statement = con.createStatement();			

			ResultSet res = statement.executeQuery("SELECT label, num FROM TagCount WHERE resourceId = "+resourceid);
			while(res.next()){
				cloud.put(res.getString("label").trim(), res.getInt("num"));
			}
			
			statement.close();
			con.commit();
			con.close();
			
		}catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
		
		return cloud;
	}

	public HashMap<String,Integer> tagCloudByUrl(String url){
		HashMap<String,Integer> cloud = new HashMap<String, Integer>();
		
		try{			
			Connection con = DatabaseController.connectToDB();			 
			Statement statement = con.createStatement();			

			ResultSet res = statement.executeQuery("SELECT label, num FROM TagCount t, Resource r WHERE t.resourceId = r.id AND r.url = '"+url+"'");
			while(res.next()){
				cloud.put(res.getString("label").trim(), res.getInt("num"));
			}
			
			statement.close();
			con.commit();
			con.close();
			
		}catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
		
		return cloud;
	}

	public HashMap<String,Integer> tagCloudBySameDigest(String url){
		HashMap<String,Integer> cloud = new HashMap<String, Integer>();
		
		try{			
			Connection con = DatabaseController.connectToDB();			 
			Statement statement = con.createStatement();			

			ResultSet res = statement.executeQuery("SELECT label, SUM(num) as count FROM TagCount t, Resource r1, Resource r2 " +
					"WHERE r1.url = '"+url+"' AND r1.documentdigest = r2.documentdigest AND r2.id = t.resourceId " +
							"GROUP BY label");
			while(res.next()){
				cloud.put(res.getString("label").trim(), res.getInt("count"));
			}
			
			statement.close();
			con.commit();
			con.close();
			
		}catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
		
		return cloud;
	}

	
	public HashMap<String,Integer> tagCloudBySameBib(String url){
		HashMap<String,Integer> cloud = new HashMap<String, Integer>();
		
		try{			
			Connection con = DatabaseController.connectToDB();			 
			Statement statement = con.createStatement();			

			ResultSet res = statement.executeQuery("SELECT label, SUM(num) as count FROM TagCount t, Resource r1, Resource r2 " +
					"WHERE r1.url = '"+url+"' AND r1.bibtexid = r2.bibtexid AND r2.id = t.resourceId " +
							"GROUP BY label");
			while(res.next()){
				cloud.put(res.getString("label").trim(), res.getInt("count"));
			}
			
			statement.close();
			con.commit();
			con.close();
			
		}catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
		
		return cloud;
	}
}
